﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;
using System.Data.SQLite;
using Spore.Interaction.Client;
using Newtonsoft.Json.Linq;
using System.Data;

namespace Scenic
{
    public class ScenicDB
    {
        //更新定时器
        private Timer _updateTimer;

        private string _syncurl
        {
            get
            {
                return string.Format("/sync/{0}", AppID);
            }
        }

        private string _dataurl
        {
            get
            {
                return string.Format("/data/{0}", AppID);
            }
        }

        private BaseHttpClient _httpClient;

        private SQLiteConnection _sqliteconn;

        public string Host { get; private set; }

        public List<app_struct> Structs { get; private set; }

        public string AppID { get; private set; }


        public ScenicDB(string host, string appid)
        {
            this.Host = host;
            this.AppID = appid;
            this._httpClient = new BaseHttpClient(host);
        }

        //初始化内存数据库
        public void Initialize()
        {
            //Data Source=:memory:;Version=3; Cache Size = 3000;New=True;Pooling=False;Max Pool Size=100;LongNames = 0; Timeout = 1000; NoTXN = 0; SyncPragma = NORMAL; StepAPI = 0
            _sqliteconn = new SQLiteConnection("Data Source=:memory:;Version=3;");
            _sqliteconn.Open();

            //获取远程数据结构并同步到本地
            var result = _httpClient.CommonRequest<wrapper>(_syncurl, new Dictionary<string, string>());
            this.Structs = result.value;

            foreach (var strc in result.value)
            {
                //构造表创建语句
                var columns = string.Join(",", strc.columns.Select(x => string.Format("{0} {1}", x.Field, x.Type)));
                var sql = string.Format("CREATE TABLE {0}({1})", strc.tablename, columns);

                SQLiteCommand cmd = new SQLiteCommand(sql, _sqliteconn);

                cmd.ExecuteNonQuery();
            }

        }

        //上次更新时间
        public DateTime LastUpdateTime { get; private set; }

        //更新数据
        public void Update()
        {
            //返回数据结构确定? json 时间格式?
            foreach (var s in Structs)//表
            {
                //
                var args = new Dictionary<string, string>();
                args.Add("tn",s.tablename);
                //获取table数据
                var json = _httpClient.Request(_dataurl, args);
                JObject o = JObject.Parse(json);
                var rows = from r in o["value"] select r;

                foreach (var r in rows)//行
                {
                    Dictionary<string, string> row = new Dictionary<string, string>();
                    //字段
                    foreach (var col in s.columns)
                    {
                        row.Add(col.Field, r[col.Field].ToString());
                    }

                    //根据 row生成insert 语句
                    var colstr =string.Join("," ,row.Keys);
                    var valuestr = string.Join(",", row.Select(x => string.Format("'{0}'", x.Value)));
                    string sql = string.Format(" insert into {0} ({1}) values ({2}) ", s.tablename, colstr, valuestr);

                    SQLiteCommand cmd = new SQLiteCommand(sql, _sqliteconn);
                    cmd.ExecuteNonQuery();
                }
            }

            //测试
            SQLiteCommand c = new SQLiteCommand(" select * from com_dict ", _sqliteconn);
            SQLiteDataAdapter adapter = new SQLiteDataAdapter(c);
            DataSet ds = new DataSet();
            adapter.Fill(ds, "abc");
        }


        //执行SQL

        //query scaler

        //执行DataSet


        //远程API调用
        public string Remote(string relpath, string method = "GET", Dictionary<string, string> pars = null)
        {
            BaseHttpClient bhc = new BaseHttpClient(Host);

            string result = bhc.Request(relpath, pars, null, method);

            return result;
        }

    }
}
